SET NOCOUNT ON
USE Northgale
go
CREATE OR ALTER PROCEDURE static_search_3
       @orderid     int          = NULL,
       @status      char(1)      = NULL,
       @fromdate    date         = NULL,
       @todate      date         = NULL,
       @custid      nchar(5)     = NULL,
       @custname    nvarchar(40) = NULL,
       @city        nvarchar(25) = NULL,
       @region      nvarchar(15) = NULL,
       @prodid      int          = NULL,
       @prodname    nvarchar(40) = NULL WITH RECOMPILE AS

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder, o.EmployeeID
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.Status = @status OR @status IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER  BY o.OrderID
go
-- Run the same three searches again. Then run again, this time
-- with "Display Actual Execution Plan" enabled and check the plans.
DECLARE @d2 datetime2(3) = sysdatetime()
EXEC static_search_3 @orderid = 11000
PRINT 'Search on order ID ' + ltrim(str(datediff(ms, @d2, sysdatetime()))) + ' ms.'
go
DECLARE @d2 datetime2(3) = sysdatetime()
EXEC static_search_3 @status = 'N'
PRINT 'Search for new orders ' + ltrim(str(datediff(ms, @d2, sysdatetime()))) + ' ms.'
go
DECLARE @d2 datetime2(3) = sysdatetime()
EXEC static_search_3 @prodid = 76
PRINT 'Search on product ID ' + ltrim(str(datediff(ms, @d2, sysdatetime()))) + ' ms.'
go
